/**
 * 快速同步关键表到备份数据库
 * 同步娱乐城、副将、装备等核心业务表
 */

const mysql = require('mysql2/promise');

// 数据库配置
const SOURCE_DB = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

const BACKUP_DB = {
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: 'jack666',
  database: 'test2_nextjs',
  charset: 'utf8mb4'
};

// 关键表列表
const KEY_TABLES = [
  // 娱乐城相关表
  'casino_dice_rounds',
  'casino_chibi_rounds',
  'casino_dice_bets',
  'casino_chibi_bets',
  'casino_rock_paper_scissors',
  
  // 副将相关表
  'deputy_general_base',
  'deputy_general',
  'player_deputy',
  'deputy_general_skill_learned',
  'deputy_general_stone',
  'deputy_general_equipment',
  'player_deputy_warehouse',
  
  // 装备相关表
  'equipment_base',
  'character_equipment_inventory',
  'character_equipment',
  
  // 基础数据表
  'basic_items',
  'skill_base',
  'level_experience',
  'mount_base',
  'map_areas',
  
  // 角色相关表
  'users',
  'characters',
  'character_attributes',
  'character_inventory',
  'character_mounts',
  
  // 系统消息表
  'system_messages',
  'private_messages',
  'chat_messages',
  'announcements'
];

class QuickSync {
  constructor() {
    this.sourceConnection = null;
    this.backupConnection = null;
  }

  async connect() {
    try {
      console.log('🔄 连接数据库...');
      
      // 先连接源数据库
      console.log('  📡 连接源数据库...');
      this.sourceConnection = await mysql.createConnection(SOURCE_DB);
      await this.sourceConnection.execute("SET NAMES utf8mb4");
      await this.sourceConnection.execute("SET time_zone = '+08:00'");
      console.log('  ✅ 源数据库连接成功');
      
      // 再连接备份数据库
      console.log('  📡 连接备份数据库...');
      this.backupConnection = await mysql.createConnection(BACKUP_DB);
      await this.backupConnection.execute("SET NAMES utf8mb4");
      await this.backupConnection.execute("SET time_zone = '+08:00'");
      console.log('  ✅ 备份数据库连接成功');
      
      console.log('✅ 所有数据库连接成功');
    } catch (error) {
      console.error('❌ 数据库连接失败:', error.message);
      
      if (error.code === 'ECONNREFUSED') {
        console.error('💡 连接被拒绝，请检查：');
        console.error('   1. 数据库服务是否正在运行');
        console.error('   2. 数据库地址和端口是否正确');
        console.error('   3. 防火墙是否阻止了连接');
      } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
        console.error('💡 访问被拒绝，请检查：');
        console.error('   1. 用户名和密码是否正确');
        console.error('   2. 用户是否有足够的权限');
      } else if (error.code === 'ER_BAD_DB_ERROR') {
        console.error('💡 数据库不存在，请检查：');
        console.error('   1. 数据库名称是否正确');
        console.error('   2. 数据库是否已创建');
      }
      
      throw error;
    }
  }

  async disconnect() {
    if (this.sourceConnection) await this.sourceConnection.end();
    if (this.backupConnection) await this.backupConnection.end();
    console.log('✅ 数据库连接已关闭');
  }

  async syncTable(tableName) {
    try {
      console.log(`🔄 同步表: ${tableName}`);
      
      // 检查表是否存在
      const [sourceTables] = await this.sourceConnection.execute(`
        SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
      `, [SOURCE_DB.database, tableName]);
      
      if (sourceTables[0].count === 0) {
        console.log(`⚠️ 源数据库中不存在表 ${tableName}，跳过`);
        return { success: false, reason: '表不存在' };
      }

      // 获取源表数据
      const [rows] = await this.sourceConnection.execute(`SELECT * FROM \`${tableName}\``);
      console.log(`📊 表 ${tableName} 有 ${rows.length} 行数据`);

      if (rows.length === 0) {
        console.log(`ℹ️ 表 ${tableName} 无数据，跳过同步`);
        return { success: true, rows: 0 };
      }

      // 检查备份表是否存在，不存在则创建
      const [backupTables] = await this.backupConnection.execute(`
        SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
      `, [BACKUP_DB.database, tableName]);

      if (backupTables[0].count === 0) {
        // 获取创建表的SQL
        const [createTableResult] = await this.sourceConnection.execute(`
          SHOW CREATE TABLE \`${tableName}\`
        `);
        const createTableSQL = createTableResult[0]['Create Table'];
        await this.backupConnection.execute(createTableSQL);
        console.log(`✅ 在备份数据库中创建表 ${tableName}`);
      }

      // 清空备份表
      await this.backupConnection.execute(`TRUNCATE TABLE \`${tableName}\``);

      // 批量插入数据
      if (rows.length > 0) {
        const columns = Object.keys(rows[0]);
        const placeholders = columns.map(() => '?').join(', ');
        const columnNames = columns.map(col => `\`${col}\``).join(', ');
        const insertSQL = `INSERT INTO \`${tableName}\` (${columnNames}) VALUES (${placeholders})`;

        // 分批插入，每批1000条
        const batchSize = 1000;
        for (let i = 0; i < rows.length; i += batchSize) {
          const batch = rows.slice(i, i + batchSize);
          for (const row of batch) {
            const values = columns.map(col => row[col]);
            await this.backupConnection.execute(insertSQL, values);
          }
          console.log(`📈 表 ${tableName} 插入进度: ${Math.min(i + batchSize, rows.length)}/${rows.length}`);
        }
      }

      console.log(`✅ 表 ${tableName} 同步完成`);
      return { success: true, rows: rows.length };

    } catch (error) {
      console.error(`❌ 同步表 ${tableName} 失败:`, error.message);
      return { success: false, error: error.message };
    }
  }

  async syncAllKeyTables() {
    const results = {
      total: KEY_TABLES.length,
      success: 0,
      failed: 0,
      details: []
    };

    console.log(`🔄 开始同步 ${KEY_TABLES.length} 个关键表...\n`);

    for (const tableName of KEY_TABLES) {
      const result = await this.syncTable(tableName);
      
      if (result.success) {
        results.success++;
        results.details.push({
          table: tableName,
          status: 'success',
          rows: result.rows || 0
        });
      } else {
        results.failed++;
        results.details.push({
          table: tableName,
          status: 'failed',
          error: result.error || result.reason
        });
      }
      
      console.log(''); // 空行分隔
    }

    return results;
  }

  async verifySync() {
    console.log('\n🔍 验证同步结果...');
    
    const verificationResults = [];
    
    for (const tableName of KEY_TABLES) {
      try {
        // 检查源表是否存在
        const [sourceTables] = await this.sourceConnection.execute(`
          SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        `, [SOURCE_DB.database, tableName]);
        
        if (sourceTables[0].count === 0) {
          verificationResults.push({
            table: tableName,
            status: 'skipped',
            reason: '源表不存在'
          });
          continue;
        }

        // 检查备份表是否存在
        const [backupTables] = await this.backupConnection.execute(`
          SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        `, [BACKUP_DB.database, tableName]);
        
        if (backupTables[0].count === 0) {
          verificationResults.push({
            table: tableName,
            status: 'failed',
            reason: '备份表不存在'
          });
          continue;
        }

        // 比较行数
        const [sourceCount] = await this.sourceConnection.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
        const [backupCount] = await this.backupConnection.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
        
        const sourceRows = sourceCount[0].count;
        const backupRows = backupCount[0].count;
        const match = sourceRows === backupRows;
        
        verificationResults.push({
          table: tableName,
          status: match ? 'success' : 'failed',
          sourceRows,
          backupRows,
          match
        });

        console.log(`  ${tableName}: ${sourceRows} -> ${backupRows} ${match ? '✅' : '❌'}`);

      } catch (error) {
        console.error(`  ❌ 验证表 ${tableName} 失败:`, error.message);
        verificationResults.push({
          table: tableName,
          status: 'error',
          error: error.message
        });
      }
    }

    const successCount = verificationResults.filter(r => r.status === 'success').length;
    console.log(`\n📊 验证结果: ${successCount}/${verificationResults.length} 个表同步成功`);

    return verificationResults;
  }

  printSummary(results) {
    console.log('\n📊 同步总结:');
    console.log(`  总表数: ${results.total}`);
    console.log(`  成功: ${results.success}`);
    console.log(`  失败: ${results.failed}`);
    console.log(`  成功率: ${Math.round((results.success / results.total) * 100)}%`);

    if (results.failed > 0) {
      console.log('\n❌ 失败的表:');
      results.details
        .filter(detail => detail.status === 'failed')
        .forEach(detail => {
          console.log(`  - ${detail.table}: ${detail.error}`);
        });
    }
  }
}

// 主函数
async function main() {
  const sync = new QuickSync();
  
  try {
    await sync.connect();
    
    const results = await sync.syncAllKeyTables();
    sync.printSummary(results);
    
    await sync.verifySync();
    
    console.log('\n🎉 关键表同步完成！');
    
  } catch (error) {
    console.error('❌ 同步失败:', error);
    process.exit(1);
  } finally {
    await sync.disconnect();
  }
}

// 处理程序退出
process.on('SIGINT', async () => {
  console.log('\n🛑 同步被中断');
  process.exit(0);
});

// 运行主函数
if (require.main === module) {
  main();
}

module.exports = QuickSync;
